﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class EnsinoMedio : IDisposable
    {
        #region " Listar "

        public DataView ListarEnsinoMedio()
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarEnsinoMedio", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "ensino_medio");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Consultar "

        public DataView ConsultarEnsinoMedio(int id_ensino_medio)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarEnsinoMedio", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@id_ensino_medio", id_ensino_medio);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "ensino_medio");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public int IncluirEnsinoMedio(DataSet dsRegistro)
        {
            int PrimaryKey = 0;

            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("IncluirEnsinoMedio", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@nome", dsRegistro.Tables[0].Rows[0]["nome"]);
                sqlc.Parameters.AddWithValue("@uf_escola", dsRegistro.Tables[0].Rows[0]["uf_escola"]);
                sqlc.Parameters.AddWithValue("@cidade_escola", dsRegistro.Tables[0].Rows[0]["cidade_escola"]);
                sqlc.Parameters.AddWithValue("@ano_conclusao", dsRegistro.Tables[0].Rows[0]["ano_conclusao"]);
                sqlc.Parameters.AddWithValue("@inscricao_enem", dsRegistro.Tables[0].Rows[0]["inscricao_enem"]);
                sqlc.Parameters.AddWithValue("@ano_enem", dsRegistro.Tables[0].Rows[0]["ano_enem"]);

                PrimaryKey = Convert.ToInt32(sqlc.ExecuteScalar().ToString());
                sql.Close();
            }

            return PrimaryKey;
        }

        #endregion

        #region " Alterar "

        public void AlterarEnsinoMedio(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarEnsinoMedio", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@nome", dsRegistro.Tables[0].Rows[0]["nome"]);
                sqlc.Parameters.AddWithValue("@uf_escola", dsRegistro.Tables[0].Rows[0]["uf_escola"]);
                sqlc.Parameters.AddWithValue("@cidade_escola", dsRegistro.Tables[0].Rows[0]["cidade_escola"]);
                sqlc.Parameters.AddWithValue("@ano_conclusao", dsRegistro.Tables[0].Rows[0]["ano_conclusao"]);
                sqlc.Parameters.AddWithValue("@inscricao_enem", dsRegistro.Tables[0].Rows[0]["inscricao_enem"]);
                sqlc.Parameters.AddWithValue("@ano_enem", dsRegistro.Tables[0].Rows[0]["ano_enem"]);

                sqlc.Parameters.AddWithValue("@id_ensino_medio", dsRegistro.Tables[0].Rows[0]["id_ensino_medio"]);

                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Excluir "

        public void ExcluirEnsinoMedio(int id_ensino_medio)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirEnsinoMedio", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@id_ensino_medio", id_ensino_medio);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
